	
--EXEC SP_SAVE_TEST_DATA_ENTRY_BINNING_INFO 1014,107,'CHA_TEST_BIN',8000,'1',8000

CREATE PROC SP_SAVE_TEST_DATA_ENTRY_BINNING_INFO 
(
	@PROCESSSTEP_ID	INT,
	@LOCATION_ID	INT,
	@LOT_NUMBER 	VARCHAR(50),
	@GOOD_DIE  	    INT,
	@BIN_NUMBER     VARCHAR(50),
	@BIN_QTY		INT
		
)
AS
BEGIN
 		
	DECLARE @BIN_COLUMN_NO VARCHAR(50)
	DECLARE @INFORMATIVE_QUERYSTRING NVARCHAR(200)
	DECLARE @LOT_ID INT
	DECLARE @INFO_COLUMNSTRING NVARCHAR(100)
	DECLARE @PARAMDEFINITION NVARCHAR(100)

	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	
	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'			
	END
	BEGIN
		IF ( SELECT COUNT(*) FROM TEST_INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID )  = 0
		BEGIN
			SET @BIN_COLUMN_NO = 'BIN_'+ @BIN_NUMBER
						
			SET @INFORMATIVE_QUERYSTRING = N'INSERT INTO TEST_INFORMATIVE_BINNING (LOT_ID, GOOD_DICE,'+@BIN_COLUMN_NO + ',TIMESTAMP)
 				VALUES('+CONVERT(NVARCHAR,@LOT_ID)+','+CONVERT(NVARCHAR,@GOOD_DIE)+','+CONVERT(NVARCHAR,@BIN_QTY)+',current_timestamp);'
				
			EXECUTE SP_EXECUTESQL @INFORMATIVE_QUERYSTRING  -- INSERTS INTO INFORMATIVE BINNING
			
			SET @INFORMATIVE_QUERYSTRING = null
			SET @BIN_COLUMN_NO  = null

		END
		ELSE
		BEGIN		
						
			SET @BIN_COLUMN_NO = 'BIN_'+ @BIN_NUMBER
			SET @INFO_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_COLUMN_NO) + ' = ' + CONVERT(NVARCHAR,@BIN_QTY) +',Good_Dice =' +CONVERT(NVARCHAR,@GOOD_DIE)  
			SET @INFORMATIVE_QUERYSTRING = N'UPDATE TEST_INFORMATIVE_BINNING SET '+@INFO_COLUMNSTRING+' WHERE LOT_ID = @LOT_ID' 
			SET @PARAMDEFINITION = N'@LOT_ID INT'
					
			EXECUTE SP_EXECUTESQL @INFORMATIVE_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID
						
			SET @INFORMATIVE_QUERYSTRING = null
			SET @BIN_COLUMN_NO  = null
		END
	END
END
